﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using WASM.WebSite.DataEntities;
using System.Text;
using System.Data;
using System.Text.RegularExpressions;

namespace WASM.WebSite.Models
{
	public class SqlRepozitory
	{
		string _connectionString;

		public SqlRepozitory(string connectionString)
		{
			_connectionString = connectionString;
		}

		public static SqlRepozitory Create(Guid azureStorageId)
		{
			AzureStorage azureStorage = AzureStorageUserSettingsRepository.Single(azureStorageId);
			return new SqlRepozitory(azureStorage.asUrl);
		}

		public bool TestConnection(out string database, out string serverVersion, out string errorMessage)
		{
			database = null;
			serverVersion = null;
			try
			{
				using (SqlConnection cn = new SqlConnection(_connectionString))
				{
					cn.Open();
					database = cn.Database;
					serverVersion = cn.ServerVersion;
					errorMessage = null;
					return true;
				}
			}
			catch (Exception ex)
			{
				errorMessage = ex.Message;
				return false;
			}
		}

		public string[] GetTables()
		{
			using (SqlConnection cn = new SqlConnection(_connectionString))
			{
				List<string> tables = new List<string>();
				SqlCommand cmd = new SqlCommand("Select name from sys.databases", cn);
				cn.Open();
				SqlDataReader reader = cmd.ExecuteReader();

				while (reader.Read())
				{
					tables.Add((string)reader[0]);
				}
				return tables.ToArray();
			}
		}

		public DataTable QueryExecute(string query, string databaseName)
		{
			using (SqlConnection cn = new SqlConnection(ChangeInitCatalog(_connectionString, databaseName)))
			{
				cn.Open();
				SqlDataAdapter dataAdapter = new SqlDataAdapter(query, cn);
				DataTable result = new DataTable();
				dataAdapter.Fill(result);
				return result;
			}
		}

		private string ChangeInitCatalog(string conntectionString, string databaseName)
		{
			string initialCatalog = "Initial Catalog = " + databaseName;
			Regex ex = new Regex(@"Initial\sCatalog=(\w*)");
			if (ex.IsMatch(conntectionString))
				return ex.Replace(conntectionString, initialCatalog);
			else
				return conntectionString + ";" + initialCatalog;
		}
	}
}
